package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractPayment;
import com.huabo.contract.oracle.entity.TblCyhwUnit;

public class TblContractPaymentMapperSqlConfig {


    public String selectPaymentManagemenByPageInfo(PageInfo<TblContractPayment> pageInfo, String orgid, TblContractPayment payment, TblCyhwUnit unit) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM TBL_CONTRACT_PAYMENT TCP " +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.CONTRACTID = TCU.CONTRACTID " +
                "LEFT JOIN TBL_CYHW_PROJECTBUDGET TCPB ON TCP.BUDGETID = TCPB.BUDGETID WHERE TCP.LINKORG = "+orgid+"");
        if (payment.getCreatestaff() != null) {
            sbSql.append(" AND TCP.CREATESTAFF = " + payment.getCreatestaff());
        }
        if (payment.getPaymenttitle() != null && !"".equals(payment.getPaymenttitle())) {
            sbSql.append(" AND TCP.PAYMENTTITLE LIKE '%" + payment.getPaymenttitle() + "%'");
        }
        if (payment.getContractno() != null && !"".equals(payment.getContractno())) {
            sbSql.append(" AND TCU.CONTRACTNO LIKE '%" + payment.getContractno() + "%'");
        }

        if (payment.getContractname() != null && !"".equals(payment.getContractname())) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + payment.getContractname() + "%'");
        }

        if (payment.getBudgetname() != null && !"".equals(payment.getBudgetname())) {
            sbSql.append(" AND TCPB.BUDGETNAME LIKE '%" + payment.getBudgetname() + "%'");
        }

        sbSql.append(" ORDER BY TCP.PAYMENTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }


    public String findPaymentManagemenByPageInfoCount(String orgid, TblContractPayment payment, TblCyhwUnit unit) {
        StringBuffer sbSql = new StringBuffer("SELECT count(*) FROM TBL_CONTRACT_PAYMENT TCP\n" +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.CONTRACTID = TCU.CONTRACTID \n" +
                "LEFT JOIN TBL_CYHW_PROJECTBUDGET TCPB ON TCP.BUDGETID = TCPB.BUDGETID WHERE TCP.LINKORG = "+orgid+"");
        if (payment.getCreatestaff() != null) {
            sbSql.append(" AND TCP.CREATESTAFF = " + payment.getCreatestaff());
        }
        if (payment.getPaymenttitle() != null && !"".equals(payment.getPaymenttitle())) {
            sbSql.append(" AND TCP.PAYMENTTITLE LIKE '%" + payment.getPaymenttitle() + "%'");
        }
        if (payment.getContract() != null) {
            if (payment.getContract().getContractno() != null && !"".equals(payment.getContract().getContractno())) {
                sbSql.append(" AND TCU.CONTRACTNO LIKE '%" + payment.getContract().getContractno() + "%'");
            }

            if (payment.getContract().getContractname() != null && !"".equals(payment.getContract().getContractname())) {
                sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + payment.getContract().getContractname() + "%'");
            }
        }

        if (payment.getPaymentmemo() != null && !"".equals(payment.getPaymentmemo())) {
            sbSql.append(" AND TCPB.BUDGETNAME LIKE '%" + payment.getPaymentmemo() + "%'");
        }

        sbSql.append(" ORDER BY TCP.PAYMENTID DESC");
        String sql = sbSql.toString();
        return sql;
    }



    public String updatePaymentInfo(TblContractPayment oldPayment) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_PAYMENT SET PAYMENTTITLE = '"+oldPayment.getPaymenttitle()+"'");

        if(oldPayment.getNodeid() != null && !"".equals(oldPayment.getNodeid())) {
            sql.append(" , NODEID = '"+oldPayment.getNodeid()+"'");
        }
        if(oldPayment.getBudgetid() != null && !"".equals(oldPayment.getBudgetid())) {
            sql.append(" , BUDGETID = '"+oldPayment.getBudgetid()+"'");
        }
        if(oldPayment.getContractid() != null && !"".equals(oldPayment.getContractid())) {
            sql.append(" , CONTRACTID = '"+oldPayment.getContractid()+"'");
        }
        if(oldPayment.getLinkorg() != null && !"".equals(oldPayment.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldPayment.getLinkorg()+"'");
        }
        if(oldPayment.getApplystaff() != null && !"".equals(oldPayment.getApplystaff())) {
            sql.append(" , APPLYSTAFF = '"+oldPayment.getApplystaff()+"'");
        }
        if(oldPayment.getApplyorg() != null && !"".equals(oldPayment.getApplyorg())) {
            sql.append(" , APPLYORG = '"+oldPayment.getApplyorg()+"'");
        }
        if(oldPayment.getPaymentrecord() != null && !"".equals(oldPayment.getPaymentrecord())) {
            sql.append(" , PAYMENTRECORD = '"+oldPayment.getPaymentrecord()+"'");
        }
        if(oldPayment.getPaymenttype() != null && !"".equals(oldPayment.getPaymenttype())) {
            sql.append(" , PAYMENTTYPE = '"+oldPayment.getPaymenttype()+"'");
        }
        if(oldPayment.getPaymentlatedate() != null && !"".equals(oldPayment.getPaymentlatedate())) {
            sql.append(" ,PAYMENTLATEDATE = TO_DATE('"+DateUtil.parseDate(oldPayment.getPaymentlatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldPayment.getPaymentmemo() != null && !"".equals(oldPayment.getPaymentmemo())) {
            sql.append(" , PAYMENTMEMO = '"+oldPayment.getPaymentmemo()+"'");
        }
       if(oldPayment.getPaymentstatus()!=null) {
            sql.append(" , PAYMENTSTATUS = '"+oldPayment.getPaymentstatus()+"'");
        }
        if(oldPayment.getApplydate() != null && !"".equals(oldPayment.getApplydate())) {
            sql.append(" ,APPLYDATE = TO_DATE('"+DateUtil.parseDate(oldPayment.getApplydate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");

        }
        if(oldPayment.getCounterbank() != null && !"".equals(oldPayment.getCounterbank())) {
            sql.append(" , COUNTERBANK = '"+oldPayment.getCounterbank()+"'");
        }
        if(oldPayment.getOrgbank() != null && !"".equals(oldPayment.getOrgbank())) {
            sql.append(" , ORGBANK = '"+oldPayment.getOrgbank()+"'");
        }
        if(oldPayment.getInvoiceid() != null && !"".equals(oldPayment.getInvoiceid())) {
            sql.append(" , INVOICEID = '"+oldPayment.getInvoiceid()+"'");
        }

        sql.append(" WHERE PAYMENTID = '"+oldPayment.getPaymentid()+"'");
        return sql.toString();
    }

    public String savePaymenInfo(TblContractPayment payment) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_PAYMENT (PAYMENTID");
        StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval");

        if(payment.getNodeid() != null) {
            column.append(",NODEID");
            value.append(",'"+payment.getNodeid()+"'");
        }
        if(payment.getBudgetid() != null) {
            column.append(",BUDGETID");
            value.append(",'"+payment.getBudgetid()+"'");
        }
        if(payment.getContractid() != null) {
            column.append(",CONTRACTID");
            value.append(",'"+payment.getContractid()+"'");
        }
        if(payment.getCreatestaff() != null) {
            column.append(",CREATESTAFF");
            value.append(",'"+payment.getCreatestaff()+"'");
        }
        if(payment.getLinkorg() != null) {
            column.append(",LINKORG");
            value.append(",'"+payment.getLinkorg()+"'");
        }
        if(payment.getPaymenmoney() != null) {
            column.append(",PAYMENMONEY");
            value.append(",'"+payment.getPaymenmoney()+"'");
        }
        if(payment.getPaymenttitle() != null) {
            column.append(",PAYMENTTITLE");
            value.append(",'"+payment.getPaymenttitle()+"'");
        }
        if(payment.getApplystaff() != null) {
            column.append(",APPLYSTAFF");
            value.append(",'"+payment.getApplystaff()+"'");
        }
        if(payment.getApplyorg() != null) {
            column.append(",APPLYORG");
            value.append(",'"+payment.getApplyorg()+"'");
        }
        if(payment.getPaymentrecord() != null) {
            column.append(",PAYMENTRECORD");
            value.append(",'"+payment.getPaymentrecord()+"'");
        }
        if(payment.getPaymenttype() != null) {
            column.append(",PAYMENTTYPE");
            value.append(",'"+payment.getPaymenttype()+"'");
        }
        if(payment.getPaymentlatedate() != null) {
            column.append(",PAYMENTLATEDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(payment.getPaymentlatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(payment.getPaymentmemo() != null) {
            column.append(",PAYMENTMEMO");
            value.append(",'"+payment.getPaymentmemo()+"'");
        }
        if(payment.getPaymentstatus() != null) {
            column.append(",PAYMENTSTATUS");
            value.append(",'"+payment.getPaymentstatus()+"'");
        }
        if(payment.getApplydate() != null) {
            column.append(",APPLYDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(payment.getApplydate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(payment.getCounterbank() != null) {
            column.append(",COUNTERBANK");
            value.append(",'"+payment.getCounterbank()+"'");
        }
        if(payment.getOrgbank() != null) {
            column.append(",ORGBANK");
            value.append(",'"+payment.getOrgbank()+"'");
        }
        if(payment.getInvoiceid() != null) {
            column.append(",INVOICEID");
            value.append(",'"+payment.getInvoiceid()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }
}
